package com.le.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;


public class InsertDataToSql {
    String hostIdAddress;       //连接的主机数据库ip地址
    String databaseName = "";        //数据库名
    String SQL;                //SQL语句

    public static void InsertStudentDataRequest(String hostIdAddress, String databaseName, String[][] data) throws SQLException {
        String url = "jdbc:mysql://" + hostIdAddress + ":3306/" +
                databaseName + "?useSSL=true&characterEncoding=utf-8";

        Connection connection = DriverManager.getConnection(url, "zhang", "123456");

        // sql前缀
        String prefix = "insert into student values ";
        // 保存sql后缀
        StringBuffer suffix = new StringBuffer();
        for (String[] datum : data) {
            // 构建sql后缀
            suffix.append("(" + datum[0] + ",'" + datum[1] + "', '" + datum[2] + "', '" + datum[3] +
                    "', '" + datum[4] + "', '" + datum[5] + "', '" + datum[6] + "'),");
        }
        // 构建完整sql
        String sql = prefix + suffix.substring(0, suffix.length() - 1);
        System.out.println(sql);
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();

    }

    public static void InsertTestGradeToSql(String hostIdAddress, String databaseName) throws SQLException {
        String url = "jdbc:mysql://" + hostIdAddress + ":3306/" +
                databaseName + "?useSSL=true&characterEncoding=utf-8";

        Connection connection = DriverManager.getConnection(url, "zhang", "123456");

        int[] data = RandomAdd.getTestGrade(100000);

        //取学号
        Query query = new Query(hostIdAddress);
        query.setDatabaseName(databaseName);
        query.setSQL("select sno from student");
        String[][] snoSet = query.getRecord();

        int dataIndex = 0;

        // sql前缀
        String prefix = "insert into sc values ";
        // 保存sql后缀
        StringBuffer suffix = new StringBuffer();
        for (int i = 1; i < snoSet.length + 1; i++) {
            String sno = snoSet[i-1][0];
            for (int j = 0; j < 4; j++) {
                suffix.append("('" + sno + "', " + j + ", " + data[dataIndex] + "), ");
                dataIndex++;
            }
        }

        // 构建完整sql
        String sql = prefix + suffix.substring(0, suffix.length() - 2);
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
    }

    public void setDatabaseName(String s) {
        databaseName = s.trim();
    }

    public void setSQL(String SQL) {
        this.SQL = SQL.trim();
    }

//    public static void main(String[] args) {
//        Long begin = System.currentTimeMillis();
//        System.out.println("开始插入数据...");
//        try {
//            InsertTestGradeToSql("10.69.104.238", "sams");
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        // 结束时间
//        Long end = System.currentTimeMillis();
//        System.out.println("插入" +100000 + "条数据数据完成！");
//        System.out.println("耗时 : " + (end - begin) / 1000 + " 秒");
//    }
}
